﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

/// <summary>
/// Summary description for InterviewMethord
/// </summary>
public class InterviewMethord
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
    public InterviewMethord()
    {
    }

    public void connClose()
    {
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
        }
    }

    public DataTable ReturnInfomationInterview(int _InterviewID)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnInformationInterview @InterviewID", conn);
            comm.Parameters.AddWithValue("@InterviewID", _InterviewID);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return tb;
    }

    public SqlDataReader ReturnInfomationInterviews(string _InterviewID)
    {
        SqlDataReader sqldr = null;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnInterviewID @InterviewID", conn);
            comm.Parameters.AddWithValue("@InterviewID", _InterviewID);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            sqldr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return sqldr;
    }


    public bool UpdateStatusApplicant(string _InterviewID, int _Status)
    {
        bool result = false;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE UpdateStatusInterview @InterviewID, @Status", conn);
            comm.Parameters.AddWithValue("@InterviewID", _InterviewID);
            comm.Parameters.AddWithValue("@Status", _Status);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            int check = comm.ExecuteNonQuery();
            if (check > 0)
            {
                result = true;
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return result;
    }
    public bool UpdateStatusInterview(int _InterviewID, int _Status)
    {
        bool result = false;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE UpdateStatusInterview @InterviewID, @Status", conn);
            comm.Parameters.AddWithValue("@InterviewID", _InterviewID);
            comm.Parameters.AddWithValue("@Status", _Status);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            int check = comm.ExecuteNonQuery();
            if (check > 0)
            {
                result = true;
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return result;
    }




    public SqlDataReader ReturnInterviewer()
    {
        SqlDataReader Sqldr = null;
        try
        {
            SqlCommand comm = new SqlCommand("Select * from SelectElementInterviewer", conn);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            Sqldr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return Sqldr;
    }

    public SqlDataReader ReturnVacancy()
    {
        SqlDataReader Sqldr = null;
        try
        {
            SqlCommand comm = new SqlCommand("Select * from SelectElementVacancy", conn);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            Sqldr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return Sqldr;
    }

    public SqlDataReader ReturnApplicantID()
    {
        SqlDataReader Sqldr = null;
        try
        {
            SqlCommand comm = new SqlCommand("Select * from SelectElementApplicant", conn);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            Sqldr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return Sqldr;
    }

    public bool UpdateInterview(int _InterviewID, string _InterviewerID, string _VacancyID, string _ApplicantID, DateTime _DateInterview, string _TimeDetails, int _Status)
    {
        bool result = false;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE UpdateTableInterview @InterviewID, @InterviewerID, @VacancyID, @ApplicantID, @DateInterview, @TimeDetails, @Status", conn);
            comm.Parameters.AddWithValue("@InterviewID", _InterviewID);
            comm.Parameters.AddWithValue("@InterviewerID", _InterviewerID);
            comm.Parameters.AddWithValue("@VacancyID", _VacancyID);
            comm.Parameters.AddWithValue("@ApplicantID", _ApplicantID);
            comm.Parameters.AddWithValue("@DateInterview", _DateInterview);
            comm.Parameters.AddWithValue("@TimeDetails", _TimeDetails);
            comm.Parameters.AddWithValue("@Status", _Status);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            int check = comm.ExecuteNonQuery();
            if (check > 0)
            {
                result = true;
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return result;
    }
    public string ReturnEmail(string _ApplicantID)
    {
        string email = "";

        try
        {
            SqlCommand cmm = new SqlCommand("EXECUTE ReturnEmail @ApplicantID", conn);
            cmm.Parameters.AddWithValue("@ApplicantID", _ApplicantID);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataReader da = cmm.ExecuteReader();
            while (da.Read())
            {
                email = da.GetString(0);

            }

        }
        catch (Exception ex)
        {

        }
        finally
        {
            connClose();
        }
        return email;

    }
}